Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# ROUND

Depending on the syntax you specify, the ROUND function performs a numeric operation or a date and time operation. Because the syntax for the ROUND function differs for each type of operation, there are two topics for the ROUND function:

- ROUND (for dates and time)

- ROUND (for numbers)

## ROUND (for dates and time)

When a DATETIME expression is specified as an argument, the ROUND function returns a date and time value rounded to a specified date format. When you do not specify a format, the date and time value is rounded to the nearest day.

Return Value

DATETIME

Syntax

ROUND(datetime_exp, format)

Arguments

datetime-exp

  An expression that identifies a date and time number.

format

  A text expression that specifies one of the format models shown in the following table. A format model indicates how the date and time number should be rounded.

  Table 21-1 Format Models for ROUND for Dates and Time

  Format Model | Description
  --- | ---
  CC SCC | One greater than the first two digits of a 4-digit year to indicate the next century. For example, `1900` becomes `2000`. `S` indicates that BC dates are marked with a negative (`-`) prefix`.`
  D DAY DY | Starting day of the week (1 to 7). The day of the week that is number 1 is controlled by NLS_TERRITORY.
  DD | Day of month (1 to 31).
  DDD | Day of year (1 to 366).
  HH HH12 | Hour of day (1 to 12).
  HH24 | Hour of day (0 to 23).
  IW | Same day of the week as the first day of the ISO year.
  IYY IY I | Last 3, 2, or 1 digit(s) of ISO year.
  IYYY | 4-digit year based on the ISO standard.
  J | Julian day; that is, the number of days since January 1, 4712 BC.
  MI | Minute (0 to 59).
  MM | Two-digit numeric abbreviation of month (01 to 12, where January is 01); month rounds up on the sixteenth day.
  MON | Abbreviated name of the month; month rounds up on the sixteenth day.
  MONTH | Name of the month padded with blanks to 9 characters; month rounds up on the sixteenth day.
  Q | Quarter of year (1, 2, 3, 4; JAN to MAR is Q1); quarter rounds up on the sixteenth day of the second month of the quarter.
  RM | Roman numeral month (`I` to `XII`, where January is I); month rounds up on the sixteenth day.
  WW | Same day of the week as the first day of the year.
  W | Same day of the week as the first day of the month.
  YEAR SYEAR | Nearest year, spelled out (rounds up on July 1). `S` indicates that BC dates are marked with a negative (`-`) prefix.
  YYYY SYYYY | Nearest 4-digit year (rounds up on July 1). `S` indicates that BC dates are marked with a negative (`-`) prefix.
  YYY YY Y | Last 3, 2, or 1 digit(s) of nearest year (rounds up on July 1).

Examples

Example 21-8 Rounding to the Nearest Year

When the value of the NLS_DATE_FORMAT option is `DD-MON-YY`, then this statement:

    SHOW ROUND ('27-OCT-92','year')

returns this value:

    01-JAN-93

## ROUND (for numbers)

When a number is specified as an argument, the ROUND function returns the number rounded to the nearest multiple of a second number you specify or to the number of decimal places indicated by the second number.

Return Value

DECIMAL (when the round type is MULTIPLE)

NUMBER (when the round type is DECIMAL)

Syntax

ROUND(number_exp roundvalue) [MULTIPLE|DECIMAL]

Arguments

number_exp

  An expression that identifies the number to round.

roundvalue

  A value that specifies the basis for rounding.

  When the round type is MULTIPLE:

  - number_exp is rounded to the nearest multiple of roundvalue.

  - roundvalue can be an `INTEGER` or decimal number.

  When the round type is DECIMAL:

  - roundvalue specifies the number of places to the right or left of the decimal point to which number_exp should be rounded. When roundvalue is positive, digits to the right of the decimal point are rounded. When it is negative, digits to the left of the decimal point are rounded.

  - When roundvalue is omitted, number_exp is rounded to `0` decimal places.

  - roundvalue must be an `INTEGER`.

MULTIPLE

  (Default) Specifies that rounding is performed by rounding to the nearest multiple of roundvalue.

DECIMAL

  Specifies that rounding is performed by rounding to the number of decimal places indicated by roundvalue.

Notes

Using ROUND to Compare Expressions

A DECIMAL value might be stored in a slightly different form than shows up at the level of significant digits you are using. This small difference can cause unexpected results when you are comparing two expressions. The problem can occur even when you are comparing INTEGER expressions that involve calculations because many calculations are done only after converting INTEGER values to DECIMAL values. You do not generally see the difference in reports because reports usually show only two or three decimal places.

For example, when you compare two numbers with the EQ or NE operators, you probably want to ignore any difference caused by the least significant digits. When `expense` was stored as `100.00000001`, the least significant digit would not be ignored by the simple form of the comparison.

The statement

    SHOW expense EQ 100.00

produces the following result.

    NO

However, you can use ROUND to force EQ or NE to ignore the least significant digits.

    SHOW ROUND(expense, .01) EQ 100.00

This statement produces the following result.

    YES

Using ABS to Compare Expressions

When speed of calculation is important in your application, you may want to use the ABS function with LT to compare numbers, instead of using ROUND with EQ or NE.

Examples

Example 21-9 Rounding to Different Multiples

The following statements show the results of rounding the expression `2/3` to different multiples. The value of the DECIMALS setting is `2`.

The statement

    SHOW ROUND(2/3, .01)

produces the following result.

    0.67

The statement

    SHOW ROUND(2/3, .1)

produces the following result.

    0.70

The statement

    SHOW ROUND(2/3, .5)

produces the following result.

    0.50

Example 21-10 Rounding to the Nearest Thousand

The following example shows `sales` rounded to the nearest thousand.

    LIMIT month TO FIRST 4
    LIMIT district TO FIRST 1
    REPORT ROUND(sales 1000)

These statements produce the following output.

    DISTRICT: BOSTON
                   -------------ROUND(SALES 1000)-------------
                   -------------------MONTH-------------------
    PRODUCT          Jan95      Feb95      Mar95      Apr95
    -------------- ---------- ---------- ---------- ----------
    Tents           32,000.00  33,000.00  43,000.00  58,000.00
    Canoes          66,000.00  76,000.00  92,000.00 126,000.00
    Racquets        52,000.00  57,000.00  59,000.00  69,000.00
    Sportswear      53,000.00  59,000.00  63,000.00  68,000.00
    Footwear        91,000.00  87,000.00 100,000.00 108,000.00

Example 21-11 Rounding to the Nearest Multiple of 12

To show `units` rounded to the nearest multiple of 12, use the following statements.

    LIMIT month TO FIRST 4
    LIMIT district TO FIRST 1
    REPORT DECIMAL 0 ROUND(units 12)

These statements produce the following output.

    DISTRICT: BOSTON
                   --------------ROUND(UNITS 12)--------------
                   -------------------MONTH-------------------
    PRODUCT          Jan95      Feb95      Mar95      Apr95
    -------------- ---------- ---------- ---------- ----------
    Tents                 204        204        264        360
    Canoes                348        396        480        660
    Racquets              996      1,080      1,116      1,308
    Sportswear          1,092      1,212      1,296      1,404
    Footwear            2,532      2,400      2,772      2,976

Example 21-12 Rounding to Decimal Places

The following statements show the results of rounding `15.193` to various decimal places.

The statement

    ROUND(15.193, 1)

produces the following result

    15.2

The statement

    ROUND(15.193, -1)

produces the following result

    20